package com.techzhi.address_web;

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.junit.jupiter.api.Test;

import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.security.MessageDigest;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.stream.Collectors;

public class GenerateSQL {

    // 高德地图API配置
    private static final String AMAP_KEY = getAmapKey(); // 从环境变量或直接配置获取
    private static final String AMAP_BASE_URL = "https://restapi.amap.com/v3/config/district";
    
    // 缓存配置
    private static final String CACHE_DIR = "/Users/shouzhi/techzhi/project/gitee/techzhi-common/address-system/cache/";
    private static final long CACHE_EXPIRE_HOURS = 24; // 缓存24小时过期
    private static final Map<String, String> memoryCache = new ConcurrentHashMap<>(); // 内存缓存
    private static int apiCallCount = 0; // API调用计数器
    private static int cacheHitCount = 0; // 缓存命中计数器
    
    /**
     * 获取高德API Key
     * 优先从环境变量获取，如果没有则使用硬编码的Key
     */
    private static String getAmapKey() {
        String key = System.getenv("AMAP_API_KEY");
        if (key != null && !key.isEmpty()) {
            return key;
        }
        // 请替换为你的实际API Key
        return "07f189ecd1b56b096ac51860eecc5a06";
    }
    
    // 数据模型类
    @Data
    @NoArgsConstructor 
    @AllArgsConstructor
    static class AreaData {
        private String code;
        private String name;
        private String parentCode;
        private int level; // 1-省 2-市 3-区县
    }
    
    /**
     * 测试缓存功能
     */
    @Test
    public void testCacheFunction() {
        try {
            System.out.println("=== 测试缓存功能 ===");
            
            // 初始化缓存
            initCacheDirectory();
            apiCallCount = 0;
            cacheHitCount = 0;
            
            System.out.println("第一次调用API（应该不命中缓存）:");
            String response1 = callAmapApiWithCache("", 1, true);
            
            System.out.println("第二次调用相同的API（应该命中缓存）:");
            String response2 = callAmapApiWithCache("", 1, true);
            
            System.out.println("第三次调用相同的API（应该命中内存缓存）:");
            String response3 = callAmapApiWithCache("", 1, true);
            
            // 验证响应是否一致
            boolean same = response1.equals(response2) && response2.equals(response3);
            
            System.out.println("缓存测试统计:");
            System.out.println("API调用次数: " + apiCallCount);
            System.out.println("缓存命中次数: " + cacheHitCount);
            System.out.println("响应内容一致性: " + (same ? "✅ 通过" : "❌ 失败"));
            
            if (apiCallCount == 1 && cacheHitCount == 2 && same) {
                System.out.println("✅ 缓存功能测试成功！");
            } else {
                System.out.println("❌ 缓存功能测试失败！");
            }
            
        } catch (Exception e) {
            System.err.println("缓存功能测试异常: " + e.getMessage());
            e.printStackTrace();
        }
    }
    
    /**
     * 测试API Key是否有效
     */
    @Test
    public void testApiKey() {
        try {
            System.out.println("测试高德API Key...");
            System.out.println("使用的API Key: " + AMAP_KEY.substring(0, 8) + "****");
            
            String response = callAmapApiWithCache("", 1, true);
            ObjectMapper mapper = new ObjectMapper();
            JsonNode json = mapper.readTree(response);
            
            if (json.get("status").asText().equals("1")) {
                System.out.println("✅ API Key 测试成功!");
                System.out.println("返回的省份数量: " + json.get("districts").get(0).get("districts").size());
            } else {
                System.err.println("❌ API Key 测试失败!");
                System.err.println("错误信息: " + json.get("info").asText());
            }
        } catch (Exception e) {
            System.err.println("❌ API Key 测试异常: " + e.getMessage());
        }
    }

    @Test
    public void generateSQL() {
        generateSQL(true); // 默认使用缓存
    }
    
    /**
     * 生成SQL数据文件
     * @param useCache 是否使用缓存，true=使用缓存，false=实时查询
     */
    public void generateSQL(boolean useCache) {
        try {
            System.out.println("开始获取高德地图行政区划数据...");
            System.out.println("缓存模式: " + (useCache ? "启用" : "禁用"));
            
            // 0. 初始化缓存目录
            initCacheDirectory();
            apiCallCount = 0;
            cacheHitCount = 0;
            
            // 1. 获取所有行政区划数据
            List<AreaData> allAreas = fetchAllDistrictData(useCache);
            System.out.println("获取数据完成，共 " + allAreas.size() + " 条记录");
            System.out.println("缓存统计 - API调用次数: " + apiCallCount + ", 缓存命中次数: " + cacheHitCount + ", 缓存命中率: " + (cacheHitCount * 100.0 / (apiCallCount + cacheHitCount)) + "%");
            
            // 2. 分类数据
            List<AreaData> provinces = new ArrayList<>();
            List<AreaData> cities = new ArrayList<>(); 
            List<AreaData> districts = new ArrayList<>();
            
            for (AreaData area : allAreas) {
                switch (area.getLevel()) {
                    case 1: provinces.add(area); break;
                    case 2: cities.add(area); break;
                    case 3: districts.add(area); break;
                }
            }
            
            System.out.println("数据分类完成 - 省:" + provinces.size() + ", 市:" + cities.size() + ", 区县:" + districts.size());
            
            // 3. 检查并修复编码重复问题
            System.out.println("检查编码唯一性...");
            ensureUniqueDistrictCodes(allAreas);
            
            // 重新分类数据（因为编码可能已经被修改）
            provinces.clear();
            cities.clear();
            districts.clear();
            
            for (AreaData area : allAreas) {
                switch (area.getLevel()) {
                    case 1: provinces.add(area); break;
                    case 2: cities.add(area); break;
                    case 3: districts.add(area); break;
                }
            }
            
            // 4. 验证数据完整性
            System.out.println("验证数据完整性...");
            validateDataIntegrity(provinces, cities, districts);
            
            // 5. 生成SQL文件
            generateMySQLFile(provinces, cities, districts);
            generatePostgreSQLFile(provinces, cities, districts);
            
            System.out.println("SQL文件生成完成!");
            System.out.println("mysql_data.sql 和 postgresql_data.sql 已生成到 scripts/back 目录下");
            
        } catch (Exception e) {
            System.err.println("生成SQL文件时发生错误: " + e.getMessage());
            e.printStackTrace();
        }
    }
    
    /**
     * 初始化缓存目录
     */
    private void initCacheDirectory() {
        try {
            Path cacheDir = Paths.get(CACHE_DIR);
            if (!Files.exists(cacheDir)) {
                Files.createDirectories(cacheDir);
                System.out.println("创建缓存目录: " + CACHE_DIR);
            }
        } catch (Exception e) {
            System.err.println("创建缓存目录失败: " + e.getMessage());
        }
    }
    
    /**
     * 生成缓存键
     */
    private String generateCacheKey(String keywords, int subdistrict) {
        try {
            String input = keywords + "_" + subdistrict + "_" + AMAP_KEY;
            MessageDigest md = MessageDigest.getInstance("MD5");
            byte[] digest = md.digest(input.getBytes(StandardCharsets.UTF_8));
            StringBuilder sb = new StringBuilder();
            for (byte b : digest) {
                sb.append(String.format("%02x", b));
            }
            return sb.toString();
        } catch (Exception e) {
            return keywords + "_" + subdistrict;
        }
    }
    
    /**
     * 从缓存读取数据
     */
    private String readFromCache(String cacheKey) {
        // 先检查内存缓存
        if (memoryCache.containsKey(cacheKey)) {
            cacheHitCount++;
            return memoryCache.get(cacheKey);
        }
        
        try {
            Path cacheFile = Paths.get(CACHE_DIR, cacheKey + ".json");
            if (!Files.exists(cacheFile)) {
                return null;
            }
            
            // 检查缓存是否过期
            long lastModified = Files.getLastModifiedTime(cacheFile).toMillis();
            long now = System.currentTimeMillis();
            long expireTime = CACHE_EXPIRE_HOURS * 60 * 60 * 1000;
            
            if (now - lastModified > expireTime) {
                Files.deleteIfExists(cacheFile);
                return null;
            }
            
            String content = new String(Files.readAllBytes(cacheFile), StandardCharsets.UTF_8);
            // 加入内存缓存
            memoryCache.put(cacheKey, content);
            cacheHitCount++;
            return content;
            
        } catch (Exception e) {
            System.err.println("读取缓存失败: " + e.getMessage());
            return null;
        }
    }
    
    /**
     * 写入缓存
     */
    private void writeToCache(String cacheKey, String content) {
        try {
            // 写入文件缓存
            Path cacheFile = Paths.get(CACHE_DIR, cacheKey + ".json");
            Files.write(cacheFile, content.getBytes(StandardCharsets.UTF_8));
            
            // 加入内存缓存
            memoryCache.put(cacheKey, content);
            
        } catch (Exception e) {
            System.err.println("写入缓存失败: " + e.getMessage());
        }
    }
    
    /**
     * 清理过期缓存
     */
    @Test
    public void cleanExpiredCache() {
        try {
            Path cacheDir = Paths.get(CACHE_DIR);
            if (!Files.exists(cacheDir)) {
                return;
            }
            
            long now = System.currentTimeMillis();
            long expireTime = CACHE_EXPIRE_HOURS * 60 * 60 * 1000;
            int deletedCount = 0;
            
            Files.list(cacheDir)
                .filter(path -> path.toString().endsWith(".json"))
                .forEach(path -> {
                    try {
                        long lastModified = Files.getLastModifiedTime(path).toMillis();
                        if (now - lastModified > expireTime) {
                            Files.deleteIfExists(path);
                        }
        } catch (Exception e) {
            System.err.println("删除过期缓存失败: " + e.getMessage());
        }
                });
                
            System.out.println("清理过期缓存完成");
        } catch (Exception e) {
            System.err.println("清理缓存失败: " + e.getMessage());
        }
    }
    
    /**
     * 查看缓存状态
     */
    @Test
    public void viewCacheStatus() {
        try {
            Path cacheDir = Paths.get(CACHE_DIR);
            if (!Files.exists(cacheDir)) {
                System.out.println("缓存目录不存在");
                return;
            }
            
            long totalSize = 0;
            int fileCount = 0;
            long now = System.currentTimeMillis();
            long expireTime = CACHE_EXPIRE_HOURS * 60 * 60 * 1000;
            int expiredCount = 0;

            List<Path> files = Files.list(cacheDir)
                .filter(path -> path.toString().endsWith(".json"))
                .collect(Collectors.toList());
                
            for (Path file : files) {
                totalSize += Files.size(file);
                fileCount++;
                
                long lastModified = Files.getLastModifiedTime(file).toMillis();
                if (now - lastModified > expireTime) {
                    expiredCount++;
                }
            }
            
            System.out.println("=== 缓存状态 ===");
            System.out.println("缓存目录: " + CACHE_DIR);
            System.out.println("缓存文件数量: " + fileCount);
            System.out.println("缓存总大小: " + formatFileSize(totalSize));
            System.out.println("过期文件数量: " + expiredCount);
            System.out.println("内存缓存数量: " + memoryCache.size());
            System.out.println("缓存有效期: " + CACHE_EXPIRE_HOURS + "小时");
            
        } catch (Exception e) {
            System.err.println("查看缓存状态失败: " + e.getMessage());
        }
    }
    
    /**
     * 格式化文件大小
     */
    private String formatFileSize(long size) {
        if (size < 1024) {
            return size + " B";
        } else if (size < 1024 * 1024) {
            return String.format("%.2f KB", size / 1024.0);
        } else {
            return String.format("%.2f MB", size / (1024.0 * 1024.0));
        }
    }
    
    /**
     * 清空所有缓存（强制刷新）
     */
    @Test
    public void clearAllCache() {
        try {
            // 清空内存缓存
            memoryCache.clear();
            
            // 清空文件缓存
            Path cacheDir = Paths.get(CACHE_DIR);
            if (Files.exists(cacheDir)) {
                Files.list(cacheDir)
                    .filter(path -> path.toString().endsWith(".json"))
                    .forEach(path -> {
                        try {
                            Files.deleteIfExists(path);
                        } catch (Exception e) {
                            System.err.println("删除缓存文件失败: " + e.getMessage());
                        }
                    });
            }
            
            System.out.println("所有缓存已清空");
        } catch (Exception e) {
            System.err.println("清空缓存失败: " + e.getMessage());
        }
    }
    
    /**
     * 获取所有行政区划数据
     * @param useCache 是否使用缓存
     */
    private List<AreaData> fetchAllDistrictData(boolean useCache) throws Exception {
        List<AreaData> allAreas = new ArrayList<>();
        ObjectMapper mapper = new ObjectMapper();
        
                // 1. 获取所有省份
        String provinceResponse = callAmapApiWithCache("", 1, useCache);
        JsonNode provinceJson = mapper.readTree(provinceResponse);
        
        if (provinceJson.get("status").asText().equals("1")) {
            JsonNode provinces = provinceJson.get("districts").get(0).get("districts");
            
            for (JsonNode province : provinces) {
                String provinceCode = province.get("adcode").asText();
                String provinceName = province.get("name").asText();
                
                // 转换为6位编码
                String standardProvinceCode = convertTo6DigitCode(provinceCode, 1);
                allAreas.add(new AreaData(standardProvinceCode, provinceName, "", 1));
                
                // 2. 获取该省份下的所有城市
                String cityResponse = callAmapApiWithCache(provinceCode, 2, useCache);
                JsonNode cityJson = mapper.readTree(cityResponse);
                
                if (cityJson.get("status").asText().equals("1")) {
                    JsonNode cities = cityJson.get("districts").get(0).get("districts");
                    
                    for (JsonNode city : cities) {
                        String cityCode = city.get("adcode").asText();
                        String cityName = city.get("name").asText();
                        
                        String standardCityCode = convertTo6DigitCode(cityCode, 2);
                        allAreas.add(new AreaData(standardCityCode, cityName, standardProvinceCode, 2));
                        
                        // 3. 获取该城市下的所有区县
                        String districtResponse = callAmapApiWithCache(cityCode, 3, useCache);
                        JsonNode districtJson = mapper.readTree(districtResponse);
                        
                        if (districtJson.get("status").asText().equals("1")) {
                            JsonNode districts = districtJson.get("districts").get(0).get("districts");
                            
                            int districtIndex = 1; // 用于生成唯一编码的序号
                            for (JsonNode district : districts) {
                                String districtCode = district.get("adcode").asText();
                                String districtName = district.get("name").asText();
                                
                                String standardDistrictCode = convertTo6DigitCode(districtCode, 3);
                                
                                // 检查区县编码是否与城市编码重复，如果重复则生成唯一编码
                                if (standardDistrictCode.equals(standardCityCode)) {
                                    standardDistrictCode = standardCityCode + String.format("%03d", districtIndex);
                                    System.out.println("检测到重复编码，为 " + cityName + " 的 " + districtName + " 生成新编码: " + standardDistrictCode);
                                }
                                
                                allAreas.add(new AreaData(standardDistrictCode, districtName, standardCityCode, 3));
                                districtIndex++;
                            }
                        }
                        
                        // 避免请求过于频繁（使用缓存后可以适当减少等待时间）
                        Thread.sleep(50);
                    }
                }
                
                Thread.sleep(50);
            }
        }
        
        return allAreas;
    }
    
    /**
     * 带缓存的高德地图API调用
     * @param keywords 查询关键词
     * @param subdistrict 行政区划级别
     * @param useCache 是否使用缓存
     */
    private String callAmapApiWithCache(String keywords, int subdistrict, boolean useCache) throws Exception {
        String cacheKey = generateCacheKey(keywords, subdistrict);
        
        // 如果启用缓存，先尝试从缓存获取
        if (useCache) {
            String cachedResponse = readFromCache(cacheKey);
            if (cachedResponse != null) {
                System.out.println("缓存命中: " + (keywords.isEmpty() ? "全国" : keywords) + " (level=" + subdistrict + ")");
                return cachedResponse;
            }
        }
        
        // 缓存未命中或禁用缓存，调用API
        System.out.println("调用API: " + (keywords.isEmpty() ? "全国" : keywords) + " (level=" + subdistrict + ")");
        String response = callAmapApi(keywords, subdistrict);
        
        // 如果启用缓存，将响应缓存起来
        if (useCache) {
            writeToCache(cacheKey, response);
        }
        
        return response;
    }
    
    /**
     * 直接调用高德地图API（不使用缓存）
     */
    private String callAmapApi(String keywords, int subdistrict) throws Exception {
        apiCallCount++;
        StringBuilder urlBuilder = new StringBuilder(AMAP_BASE_URL);
        urlBuilder.append("?key=").append(AMAP_KEY);
        urlBuilder.append("&subdistrict=").append(subdistrict);
        urlBuilder.append("&extensions=base");
        
        if (!keywords.isEmpty()) {
            urlBuilder.append("&keywords=").append(URLEncoder.encode(keywords, StandardCharsets.UTF_8.toString()));
        }
        
        URL url = new URL(urlBuilder.toString());
        HttpURLConnection connection = (HttpURLConnection) url.openConnection();
        connection.setRequestMethod("GET");
        connection.setConnectTimeout(10000);
        connection.setReadTimeout(10000);
        
        try (BufferedReader reader = new BufferedReader(
                new InputStreamReader(connection.getInputStream(), StandardCharsets.UTF_8))) {
            StringBuilder response = new StringBuilder();
            String line;
            while ((line = reader.readLine()) != null) {
                response.append(line);
            }
            return response.toString();
        } catch (Exception e) {
            // 如果API调用失败，返回一个空的响应，让程序继续使用缓存数据
            System.err.println("API调用失败，将使用缓存数据: " + e.getMessage());
            return "{\"status\":\"0\",\"info\":\"API调用失败\",\"districts\":[]}";
        }
    }
    
    /**
     * 转换为6位标准编码
     */
    private String convertTo6DigitCode(String originalCode, int level) {
        if (originalCode.length() >= 6) {
            return originalCode.substring(0, 6);
        }
        return originalCode;
    }
    
    /**
     * 检查并修复所有编码的唯一性和长度问题
     * 严格按照VARCHAR(6)限制，所有编码必须控制在6位以内
     */
    private void ensureUniqueDistrictCodes(List<AreaData> allAreas) {
        Set<String> usedCodes = new HashSet<>();
        Map<String, Integer> cityDistrictCounter = new HashMap<>();
        Map<String, Integer> provinceCityCounter = new HashMap<>();
        
        // 第一遍：确保所有编码都不超过6位，并处理空编码
        for (AreaData area : allAreas) {
            String code = area.getCode();
            if (code == null || code.trim().isEmpty()) {
                // 生成默认编码
                code = generateDefaultCode(area);
                area.setCode(code);
            } else if (code.length() > 6) {
                area.setCode(code.substring(0, 6));
            }
        }
        
        // 第二遍：处理重复编码问题，按层级顺序处理
        // 先处理省份编码
        for (AreaData area : allAreas) {
            if (area.getLevel() == 1) { // 省份级别
                String code = area.getCode();
                if (usedCodes.contains(code)) {
                    // 省份编码重复，生成新编码
                    String newCode = generateUniqueProvinceCode(usedCodes);
                    area.setCode(newCode);
                    System.out.println("修复省份重复编码: " + area.getName() + " " + code + " -> " + newCode);
                }
                usedCodes.add(area.getCode());
            }
        }
        
        // 再处理城市编码
        for (AreaData area : allAreas) {
            if (area.getLevel() == 2) { // 城市级别
                String code = area.getCode();
                String provinceCode = area.getParentCode();
                
                if (usedCodes.contains(code) || code.equals(provinceCode)) {
                    int counter = provinceCityCounter.getOrDefault(provinceCode, 0) + 1;
                    provinceCityCounter.put(provinceCode, counter);
                    
                    // 生成新的城市编码：省份编码前2位 + 4位序号
                    String newCode = generateUniqueCityCode(provinceCode, counter, usedCodes);
                    area.setCode(newCode);
                    System.out.println("修复城市重复编码: " + area.getName() + " " + code + " -> " + newCode);
                }
                usedCodes.add(area.getCode());
            }
        }
        
        // 最后处理区县编码
        for (AreaData area : allAreas) {
            if (area.getLevel() == 3) { // 区县级别
                String code = area.getCode();
                String cityCode = area.getParentCode();
                
                if (usedCodes.contains(code) || code.equals(cityCode)) {
                    int counter = cityDistrictCounter.getOrDefault(cityCode, 0) + 1;
                    cityDistrictCounter.put(cityCode, counter);
                    
                    // 生成新的区县编码
                    String newCode = generateUniqueDistrictCode(cityCode, counter, usedCodes);
                    area.setCode(newCode);
                    System.out.println("修复区县重复编码: " + area.getName() + " " + code + " -> " + newCode);
                }
                usedCodes.add(area.getCode());
            }
        }
        
        System.out.println("编码唯一性检查完成，共处理 " + allAreas.size() + " 条记录");
    }
    
    /**
     * 生成默认编码
     */
    private String generateDefaultCode(AreaData area) {
        switch (area.getLevel()) {
            case 1: return "110000"; // 默认省份编码
            case 2: return "110100"; // 默认城市编码
            case 3: return "110101"; // 默认区县编码
            default: return "000000";
        }
    }
    
    /**
     * 生成唯一的省份编码
     */
    private String generateUniqueProvinceCode(Set<String> usedCodes) {
        for (int i = 110000; i <= 999999; i += 10000) {
            String code = String.valueOf(i);
            if (!usedCodes.contains(code)) {
                return code;
            }
        }
        return "999999"; // 兜底编码
    }
    
    /**
     * 生成唯一的城市编码
     */
    private String generateUniqueCityCode(String provinceCode, int counter, Set<String> usedCodes) {
        String baseCode = provinceCode.length() >= 2 ? provinceCode.substring(0, 2) : "11";
        String newCode = baseCode + String.format("%04d", counter);
        
        // 确保不超过6位
        if (newCode.length() > 6) {
            newCode = newCode.substring(0, 6);
        }
        
        // 如果仍然重复，尝试其他编码
        int attempts = 0;
        while (usedCodes.contains(newCode) && attempts < 1000) {
            counter++;
            newCode = baseCode + String.format("%04d", counter);
            if (newCode.length() > 6) {
                newCode = newCode.substring(0, 6);
            }
            attempts++;
        }
        
        return newCode;
    }
    
    /**
     * 生成唯一的区县编码
     */
    private String generateUniqueDistrictCode(String cityCode, int counter, Set<String> usedCodes) {
        String baseCode = cityCode.length() >= 3 ? cityCode.substring(0, 3) : "110";
        String newCode = baseCode + String.format("%03d", counter);
        
        // 确保不超过6位
        if (newCode.length() > 6) {
            newCode = newCode.substring(0, 6);
        }
        
        // 如果仍然重复，尝试其他编码
        int attempts = 0;
        while (usedCodes.contains(newCode) && attempts < 1000) {
            counter++;
            newCode = baseCode + String.format("%03d", counter);
            if (newCode.length() > 6) {
                newCode = newCode.substring(0, 6);
            }
            attempts++;
        }
        
        return newCode;
    }
    
    /**
     * 生成MySQL SQL文件
     */
    private void generateMySQLFile(List<AreaData> provinces, List<AreaData> cities, List<AreaData> districts) throws IOException {
        String filename = "/Users/shouzhi/techzhi/project/gitee/techzhi-common/address-system/scripts/back/mysql_data.sql";
        try (PrintWriter writer = new PrintWriter(new FileWriter(filename, false), true)) {
            writer.println("-- MySQL省市区地址数据");
            writer.println("-- 生成时间: " + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
            writer.println("-- 数据来源: 高德地图API");
            writer.println();
            
            // 禁用外键检查，避免删除顺序问题
            writer.println("-- 禁用外键检查");
            writer.println("SET FOREIGN_KEY_CHECKS = 0;");
            writer.println();
            
            // 清空表数据（按依赖关系顺序删除）
            writer.println("-- 清空现有数据（按依赖关系顺序）");
            writer.println("DELETE FROM district;");
            writer.println("DELETE FROM city;");
            writer.println("DELETE FROM province;");
            writer.println();
            
            // 重置自增主键
            writer.println("-- 重置自增主键");
            writer.println("ALTER TABLE district AUTO_INCREMENT = 1;");
            writer.println("ALTER TABLE city AUTO_INCREMENT = 1;");
            writer.println("ALTER TABLE province AUTO_INCREMENT = 1;");
            writer.println();
            
            // 插入省份数据
            if (!provinces.isEmpty()) {
                writer.println("-- 插入省份数据 (" + provinces.size() + "条)");
                writer.println("INSERT INTO province (code, name, create_time) VALUES");
                for (int i = 0; i < provinces.size(); i++) {
                    AreaData province = provinces.get(i);
                    writer.print("('" + province.getCode() + "', '" + escapeSql(province.getName()) + "', NOW())");
                    if (i < provinces.size() - 1) {
                        writer.println(",");
                    } else {
                        writer.println(";");
                    }
                }
                writer.println();
            } else {
                writer.println("-- 警告：没有省份数据可插入");
                writer.println();
            }
            
            // 插入城市数据
            if (!cities.isEmpty()) {
                writer.println("-- 插入城市数据 (" + cities.size() + "条)");
                writer.println("INSERT INTO city (province_code, code, name, create_time) VALUES");
                for (int i = 0; i < cities.size(); i++) {
                    AreaData city = cities.get(i);
                    writer.print("('" + city.getParentCode() + "', '" + city.getCode() + "', '" + escapeSql(city.getName()) + "', NOW())");
                    if (i < cities.size() - 1) {
                        writer.println(",");
                    } else {
                        writer.println(";");
                    }
                }
                writer.println();
            } else {
                writer.println("-- 警告：没有城市数据可插入");
                writer.println();
            }
            
            // 插入区县数据
            if (!districts.isEmpty()) {
                writer.println("-- 插入区县数据 (" + districts.size() + "条)");
                writer.println("INSERT INTO district (city_code, code, name, create_time) VALUES");
                for (int i = 0; i < districts.size(); i++) {
                    AreaData district = districts.get(i);
                    writer.print("('" + district.getParentCode() + "', '" + district.getCode() + "', '" + escapeSql(district.getName()) + "', NOW())");
                    if (i < districts.size() - 1) {
                        writer.println(",");
                    } else {
                        writer.println(";");
                    }
                }
                writer.println();
            } else {
                writer.println("-- 警告：没有区县数据可插入");
                writer.println();
            }
            
            // 重新启用外键检查
            writer.println("-- 重新启用外键检查");
            writer.println("SET FOREIGN_KEY_CHECKS = 1;");
            writer.println();
            
            writer.println("-- 数据插入完成");
            writer.println("-- 省份: " + provinces.size() + "条");
            writer.println("-- 城市: " + cities.size() + "条");  
            writer.println("-- 区县: " + districts.size() + "条");
            
            // 添加数据验证查询
            writer.println();
            writer.println("-- 数据验证查询");
            writer.println("SELECT 'province' as table_name, COUNT(*) as count FROM province");
            writer.println("UNION ALL");
            writer.println("SELECT 'city' as table_name, COUNT(*) as count FROM city");
            writer.println("UNION ALL");
            writer.println("SELECT 'district' as table_name, COUNT(*) as count FROM district;");
        }
    }
    
    /**
     * 生成PostgreSQL SQL文件
     */
    private void generatePostgreSQLFile(List<AreaData> provinces, List<AreaData> cities, List<AreaData> districts) throws IOException {
        String filename = "/Users/shouzhi/techzhi/project/gitee/techzhi-common/address-system/scripts/back/postgresql_data.sql";
        try (PrintWriter writer = new PrintWriter(new FileWriter(filename, false), true)) {
            writer.println("-- PostgreSQL省市区地址数据");
            writer.println("-- 生成时间: " + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
            writer.println("-- 数据来源: 高德地图API");
            writer.println();
            
            // 开始事务
            writer.println("-- 开始事务");
            writer.println("BEGIN;");
            writer.println();
            
            // 清空表数据（按依赖关系顺序删除）
            writer.println("-- 清空现有数据（按依赖关系顺序）");
            writer.println("DELETE FROM district;");
            writer.println("DELETE FROM city;");
            writer.println("DELETE FROM province;");
            writer.println();
            
            // 重置序列
            writer.println("-- 重置序列");
            writer.println("ALTER SEQUENCE province_id_seq RESTART WITH 1;");
            writer.println("ALTER SEQUENCE city_id_seq RESTART WITH 1;");
            writer.println("ALTER SEQUENCE district_id_seq RESTART WITH 1;");
            writer.println();
            
            // 插入省份数据
            if (!provinces.isEmpty()) {
                writer.println("-- 插入省份数据 (" + provinces.size() + "条)");
                writer.println("INSERT INTO province (code, name, create_time) VALUES");
                for (int i = 0; i < provinces.size(); i++) {
                    AreaData province = provinces.get(i);
                    writer.print("('" + province.getCode() + "', '" + escapeSql(province.getName()) + "', NOW())");
                    if (i < provinces.size() - 1) {
                        writer.println(",");
                    } else {
                        writer.println(";");
                    }
                }
                writer.println();
            } else {
                writer.println("-- 警告：没有省份数据可插入");
                writer.println();
            }
            
            // 插入城市数据  
            if (!cities.isEmpty()) {
                writer.println("-- 插入城市数据 (" + cities.size() + "条)");
                writer.println("INSERT INTO city (province_code, code, name, create_time) VALUES");
                for (int i = 0; i < cities.size(); i++) {
                    AreaData city = cities.get(i);
                    writer.print("('" + city.getParentCode() + "', '" + city.getCode() + "', '" + escapeSql(city.getName()) + "', NOW())");
                    if (i < cities.size() - 1) {
                        writer.println(",");
                    } else {
                        writer.println(";");
                    }
                }
                writer.println();
            } else {
                writer.println("-- 警告：没有城市数据可插入");
                writer.println();
            }
            
            // 插入区县数据
            if (!districts.isEmpty()) {
                writer.println("-- 插入区县数据 (" + districts.size() + "条)");
                writer.println("INSERT INTO district (city_code, code, name, create_time) VALUES");
                for (int i = 0; i < districts.size(); i++) {
                    AreaData district = districts.get(i);
                    writer.print("('" + district.getParentCode() + "', '" + district.getCode() + "', '" + escapeSql(district.getName()) + "', NOW())");
                    if (i < districts.size() - 1) {
                        writer.println(",");
                    } else {
                        writer.println(";");
                    }
                }
                writer.println();
            } else {
                writer.println("-- 警告：没有区县数据可插入");
                writer.println();
            }
            
            // 提交事务
            writer.println("-- 提交事务");
            writer.println("COMMIT;");
            writer.println();
            
            writer.println("-- 数据插入完成");
            writer.println("-- 省份: " + provinces.size() + "条");
            writer.println("-- 城市: " + cities.size() + "条");
            writer.println("-- 区县: " + districts.size() + "条");
            
            // 添加数据验证查询
            writer.println();
            writer.println("-- 数据验证查询");
            writer.println("SELECT 'province' as table_name, COUNT(*) as count FROM province");
            writer.println("UNION ALL");
            writer.println("SELECT 'city' as table_name, COUNT(*) as count FROM city");
            writer.println("UNION ALL");
            writer.println("SELECT 'district' as table_name, COUNT(*) as count FROM district;");
        }
    }
    
    /**
     * 验证数据完整性
     */
    private void validateDataIntegrity(List<AreaData> provinces, List<AreaData> cities, List<AreaData> districts) {
        System.out.println("=== 数据完整性验证 ===");
        
        // 1. 检查编码唯一性
        Set<String> allCodes = new HashSet<>();
        boolean hasDuplicate = false;
        
        for (AreaData province : provinces) {
            if (!allCodes.add(province.getCode())) {
                System.err.println("❌ 省份编码重复: " + province.getCode() + " (" + province.getName() + ")");
                hasDuplicate = true;
            }
        }
        
        for (AreaData city : cities) {
            if (!allCodes.add(city.getCode())) {
                System.err.println("❌ 城市编码重复: " + city.getCode() + " (" + city.getName() + ")");
                hasDuplicate = true;
            }
        }
        
        for (AreaData district : districts) {
            if (!allCodes.add(district.getCode())) {
                System.err.println("❌ 区县编码重复: " + district.getCode() + " (" + district.getName() + ")");
                hasDuplicate = true;
            }
        }
        
        if (!hasDuplicate) {
            System.out.println("✅ 编码唯一性检查通过");
        }
        
        // 2. 检查编码长度
        boolean hasInvalidLength = false;
        for (AreaData province : provinces) {
            if (province.getCode().length() > 6) {
                System.err.println("❌ 省份编码长度超限: " + province.getCode() + " (" + province.getName() + ")");
                hasInvalidLength = true;
            }
        }
        
        for (AreaData city : cities) {
            if (city.getCode().length() > 6) {
                System.err.println("❌ 城市编码长度超限: " + city.getCode() + " (" + city.getName() + ")");
                hasInvalidLength = true;
            }
        }
        
        for (AreaData district : districts) {
            if (district.getCode().length() > 6) {
                System.err.println("❌ 区县编码长度超限: " + district.getCode() + " (" + district.getName() + ")");
                hasInvalidLength = true;
            }
        }
        
        if (!hasInvalidLength) {
            System.out.println("✅ 编码长度检查通过");
        }
        
        // 3. 检查外键关系
        Set<String> provinceCodes = provinces.stream().map(AreaData::getCode).collect(Collectors.toSet());
        Set<String> cityCodes = cities.stream().map(AreaData::getCode).collect(Collectors.toSet());
        boolean hasInvalidForeignKey = false;
        
        for (AreaData city : cities) {
            if (!provinceCodes.contains(city.getParentCode())) {
                System.err.println("❌ 城市外键无效: " + city.getName() + " 引用的省份编码 " + city.getParentCode() + " 不存在");
                hasInvalidForeignKey = true;
            }
        }
        
        for (AreaData district : districts) {
            if (!cityCodes.contains(district.getParentCode())) {
                System.err.println("❌ 区县外键无效: " + district.getName() + " 引用的城市编码 " + district.getParentCode() + " 不存在");
                hasInvalidForeignKey = true;
            }
        }
        
        if (!hasInvalidForeignKey) {
            System.out.println("✅ 外键关系检查通过");
        }
        
        // 4. 检查名称完整性
        boolean hasEmptyName = false;
        for (AreaData province : provinces) {
            if (province.getName() == null || province.getName().trim().isEmpty()) {
                System.err.println("❌ 省份名称为空: " + province.getCode());
                hasEmptyName = true;
            }
        }
        
        for (AreaData city : cities) {
            if (city.getName() == null || city.getName().trim().isEmpty()) {
                System.err.println("❌ 城市名称为空: " + city.getCode());
                hasEmptyName = true;
            }
        }
        
        for (AreaData district : districts) {
            if (district.getName() == null || district.getName().trim().isEmpty()) {
                System.err.println("❌ 区县名称为空: " + district.getCode());
                hasEmptyName = true;
            }
        }
        
        if (!hasEmptyName) {
            System.out.println("✅ 名称完整性检查通过");
        }
        
        // 5. 统计信息
        System.out.println("=== 数据统计 ===");
        System.out.println("省份数量: " + provinces.size());
        System.out.println("城市数量: " + cities.size());
        System.out.println("区县数量: " + districts.size());
        System.out.println("总记录数: " + (provinces.size() + cities.size() + districts.size()));
        
        // 6. 检查是否有数据
        if (provinces.isEmpty() && cities.isEmpty() && districts.isEmpty()) {
            System.err.println("⚠️  警告：没有获取到任何数据，请检查API配置和网络连接");
        }
        
        System.out.println("=== 数据验证完成 ===");
    }
    
    /**
     * 测试使用缓存的SQL生成
     */
    @Test
    public void generateSQLWithCache() {
        generateSQL(true);
    }
    
    /**
     * 测试不使用缓存的SQL生成（实时查询）
     */
    @Test
    public void generateSQLWithoutCache() {
        generateSQL(false);
    }
    
    /**
     * 测试修复后的SQL生成功能
     */
    @Test
    public void testFixedSQLGeneration() {
        try {
            System.out.println("=== 测试修复后的SQL生成功能 ===");
            
            // 创建测试数据
            List<AreaData> testProvinces = Arrays.asList(
                new AreaData("110000", "北京市", "", 1),
                new AreaData("120000", "天津市", "", 1)
            );
            
            List<AreaData> testCities = Arrays.asList(
                new AreaData("110100", "北京市", "110000", 2),
                new AreaData("120100", "天津市", "120000", 2)
            );
            
            List<AreaData> testDistricts = Arrays.asList(
                new AreaData("110101", "东城区", "110100", 3),
                new AreaData("110102", "西城区", "110100", 3),
                new AreaData("120101", "和平区", "120100", 3)
            );
            
            // 测试数据验证
            System.out.println("测试数据验证功能...");
            validateDataIntegrity(testProvinces, testCities, testDistricts);
            
            // 测试编码唯一性检查
            System.out.println("\n测试编码唯一性检查...");
            List<AreaData> allTestData = new ArrayList<>();
            allTestData.addAll(testProvinces);
            allTestData.addAll(testCities);
            allTestData.addAll(testDistricts);
            
            // 添加重复编码测试
            allTestData.add(new AreaData("110000", "重复省份", "", 1)); // 重复编码
            allTestData.add(new AreaData("110101", "重复区县", "110100", 3)); // 重复编码
            
            ensureUniqueDistrictCodes(allTestData);
            
            // 重新分类数据
            List<AreaData> fixedProvinces = new ArrayList<>();
            List<AreaData> fixedCities = new ArrayList<>();
            List<AreaData> fixedDistricts = new ArrayList<>();
            
            for (AreaData area : allTestData) {
                switch (area.getLevel()) {
                    case 1: fixedProvinces.add(area); break;
                    case 2: fixedCities.add(area); break;
                    case 3: fixedDistricts.add(area); break;
                }
            }
            
            // 再次验证修复后的数据
            System.out.println("\n验证修复后的数据...");
            validateDataIntegrity(fixedProvinces, fixedCities, fixedDistricts);
            
            // 测试SQL生成
            System.out.println("\n测试SQL生成...");
            generateMySQLFile(fixedProvinces, fixedCities, fixedDistricts);
            generatePostgreSQLFile(fixedProvinces, fixedCities, fixedDistricts);
            
            System.out.println("✅ 修复后的SQL生成功能测试完成！");
            
        } catch (Exception e) {
            System.err.println("❌ 测试过程中发生错误: " + e.getMessage());
            e.printStackTrace();
        }
    }
    
    /**
     * SQL字符串转义
     */
    private String escapeSql(String input) {
        if (input == null) return "";
        return input.replace("'", "''");
    }
}
